// $Id: BaseOleDbImportPlugin.cs 66 2008-05-07 19:57:30Z nvivo $

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace DBLGen.Plugins.Import
{
    public abstract class BaseOleDbImportPlugin : BaseImportPlugin
    {
        private StringDictionary _providerTypeMap;

        protected StringDictionary ProviderTypeMap
        {
            get
            {
                if (_providerTypeMap == null)
                    _providerTypeMap = BuildProviderTypeMap();

                return _providerTypeMap;
            }
        }

        protected virtual StringDictionary BuildProviderTypeMap()
        {
            DataTable dataTypesSchema = GetSchema("DataTypes");
            StringDictionary dict = new StringDictionary();

            foreach (DataRow row in dataTypesSchema.Rows)
                dict[row["NativeDataType"].ToString()] = row["TypeName"].ToString();

            return dict;
        }

        protected DataTable GetOleDbSchema(Guid schemaGuid)
        {
            DataTable dt;

            using (OleDbConnection connection = (OleDbConnection) CreateConnection())
            {
                connection.Open();
                dt = connection.GetOleDbSchemaTable(schemaGuid, null);
                connection.Close();
            }

            return dt;
        }

        public override Table[] GetAllTables()
        {
            DataTable tablesSchema = GetOleDbSchema(OleDbSchemaGuid.Tables);
            DataTable columnsSchema = GetOleDbSchema(OleDbSchemaGuid.Columns);
            DataTable pkSchema = GetOleDbSchema(OleDbSchemaGuid.Primary_Keys);
            DataTable fkSchema = GetOleDbSchema(OleDbSchemaGuid.Foreign_Keys);

            DataTable refTables = BuildTablesTableFromSchema(tablesSchema);
            DataTable refColumns = BuildColumnsTableFromSchema(columnsSchema, pkSchema, fkSchema);

            return BuildTables(refTables, refColumns);
        }

        private DataTable BuildTablesTableFromSchema(DataTable tablesSchema)
        {
            DataTable tablesReference = CreateTablesReferenceTable();

            foreach (DataRow tRow in tablesSchema.Select("TABLE_TYPE in ('Table', 'View')", "TABLE_NAME"))
            {
                DataRow rRow = tablesReference.NewRow();
                
                rRow["tableschema"] = tRow["TABLE_SCHEMA"] as string;
                rRow["tablename"]   = tRow["TABLE_NAME"] as string;
                rRow["tabletype"]   = tRow["TABLE_TYPE"] as string;

                tablesReference.Rows.Add(rRow);
            }

            return tablesReference;
        }

        private DataTable BuildColumnsTableFromSchema(DataTable columnsSchema, DataTable pkSchema, DataTable fkSchema)
        {
            DataTable columnsReference = CreateColumnsReferenceTable();

            bool hasComputedColumn = false;

            if (columnsSchema.Columns["IS_COMPUTED"] != null)
                hasComputedColumn = true;

            foreach (DataRow cRow in columnsSchema.Rows)
            {
                DataRow rRow = columnsReference.NewRow();

                string tableSchema = cRow["TABLE_SCHEMA"].ToString();
                string tableName   = cRow["TABLE_NAME"].ToString();
                string columnName  = cRow["COLUMN_NAME"].ToString();
                string columnType  = cRow["DATA_TYPE"].ToString();

                object length, octetLength, precision, scale;

                string fkTableSchema, fkTableName, fkColumnName;

                length      = GetInt32ColumnValue(cRow["CHARACTER_MAXIMUM_LENGTH"]);
                octetLength = GetInt32ColumnValue(cRow["CHARACTER_OCTET_LENGTH"]);
                precision   = GetInt32ColumnValue(cRow["NUMERIC_PRECISION"]);
                scale       = GetInt32ColumnValue(cRow["NUMERIC_SCALE"]);

                rRow["tableschema"] = tableSchema;
                rRow["tablename"]   = tableName;
                rRow["columnorder"] = Convert.ToInt32(cRow["ORDINAL_POSITION"]);
                rRow["columnname"]  = columnName;
                rRow["type"]        = ProviderTypeMap[columnType];
                rRow["length"]      = length;
                rRow["octetlength"] = octetLength;
                rRow["precision"]   = precision;
                rRow["scale"]       = scale;
                rRow["isnullable"]  = (bool) cRow["IS_NULLABLE"];

                if (hasComputedColumn)
                    rRow["iscomputed"] = (bool) cRow["IS_COMPUTED"];
                else
                    rRow["iscomputed"] = false;
                
                rRow["ispk"] = IsPrimaryKey(pkSchema, tableSchema, tableName, columnName);
                rRow["isfk"] = IsForeignKey(fkSchema, tableSchema, tableName, columnName, out fkTableSchema, out fkTableName, out fkColumnName);

                rRow["ref_tableschema"] = fkTableSchema;
                rRow["ref_tablename"] = fkTableName;
                rRow["ref_columnname"] = fkColumnName;

                columnsReference.Rows.Add(rRow);
            }

            return columnsReference;
        }

        private static bool IsPrimaryKey(DataTable pkSchema, string tableSchema, string tableName, string columnName)
        {
            string filter;

            if (String.IsNullOrEmpty(tableSchema))
                filter = String.Format("TABLE_SCHEMA IS NULL AND TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, columnName);            
            else
                filter = String.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", tableSchema, tableName, columnName);

            DataRow[] rows = pkSchema.Select(filter);

            if (rows.Length > 0)
                return true;
            else
                return false;
        }

        private static bool IsForeignKey(DataTable fkSchema, string tableSchema, string tableName, string columnName, out string fkTableSchema, out string fkTableName, out string fkColumnName)
        {
            string filter;
            
            if (String.IsNullOrEmpty(tableSchema))
                filter = String.Format("FK_TABLE_SCHEMA IS NULL AND FK_TABLE_NAME = '{0}' AND FK_COLUMN_NAME = '{1}'", tableName, columnName);
            else 
                filter = String.Format("FK_TABLE_SCHEMA = '{0}' AND FK_TABLE_NAME = '{1}' AND FK_COLUMN_NAME = '{2}'", tableSchema, tableName, columnName);

            DataRow[] rows = fkSchema.Select(filter);

            if (rows.Length > 0)
            {
                fkTableSchema = rows[0]["PK_TABLE_SCHEMA"] as string;
                fkTableName = (string) rows[0]["PK_TABLE_NAME"];
                fkColumnName = (string) rows[0]["PK_COLUMN_NAME"];
                return true;
            }
            else
            {
                fkTableSchema = null;
                fkTableName = null;
                fkColumnName = null;
                return false;
            }
        }

        private object GetInt32ColumnValue(object o)
        {
            if (o == null)
                return DBNull.Value;

            int value;

            if (Int32.TryParse(o.ToString(), out value))
                return value;
            else
                return DBNull.Value;
        }
    }
}
